package main

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

type UserTest struct {
	gorm.Model
	Name string
	Age  int64
}

func main() {
	db, err := gorm.Open("mysql", "root:123456@(127.0.0.1:3306)/db1?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 自动创建表  自动迁移 （把结构体和数据表进行映射）
	db.AutoMigrate(&UserTest{})

	// 创建2条记录
	//u1 := UserTest{Name: "大狗", Age: 20}
	//db.Create(&u1)
	//u2 := UserTest{Name: "二狗", Age: 18}
	//db.Create(&u2)

	// 查询
	//var user UserTest
	//db.First(&user) // 查询第一条记录，赋值给user结构体变量
	//fmt.Printf("%#v\n", user)

	//var users []UserTest
	//db.Find(&users) // 查询所有数据
	//fmt.Printf("%#v\n", users)

	var user UserTest
	// 条件查询：
	// Get first matched record
	// SELECT * FROM users WHERE name = '二狗' limit 1;
	db.Where("name = ?", "二狗").First(&user)
	fmt.Println("查询name = '二狗'的第一条：", user.Name, user.Age)

	var users []UserTest

	// 分页查询
	// SELECT * FROM users WHERE name = '二狗' limt 0, 1;
	db.Where("name = ?", "二狗").Offset(0).Limit(1).Find(&users)
	fmt.Println("查询name = '二狗'的所有数据：", users)

	// <>
	// SELECT * FROM users WHERE name <> '二狗';
	db.Where("name <> ?", "二狗").Find(&users)
	fmt.Println("name不是二狗的数据：", users)

	// IN   .Count(num) 是计数，然后结果给到num
	// SELECT * FROM users WHERE name in ('二狗','二狗');
	var num int
	db.Where("name IN (?)", []string{"大狗", "二狗"}).Find(&users).Count(num)
	fmt.Println("name是大狗或者二狗的数据：", users)

	// LIKE
	// SELECT * FROM users WHERE name LIKE '%二%';
	db.Where("name LIKE ?", "%二%").Find(&users)
	fmt.Println("模糊查询name带二的：", users)

	// AND
	// SELECT * FROM users WHERE name = '二狗' AND age >= 18;
	db.Where("name = ? AND age >= ?", "二狗", "18").Find(&users)

	// Time
	// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
	//db.Where("updated_at > ?", lastWeek).Find(&users)

	// BETWEEN
	// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
	//db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

	// ORDER BY
	//db.Order("id desc").Order("age desc").Find(&users)

	// 更多查询操作参考： https://www.topgoer.cn/docs/gorm/gormchaxun

	// 如果要自己写sql，可以使用 .Raw()函数进行查询，  .Exec()函数进行执行删除或修改或新增
	db.Raw("select * from users where id = ?", 1).Scan(&user)
	var count int
	db.Raw("select count(1) from users").Scan(count)
	db.Exec("update users set name = ? where id = ?", "黑狗", 1)

}
